<?php
/**
 * Copyright (c) 2010, Slawomir Mazur <mazursllawek@gmail.com>
 * 
 * All rights reserved.
 * 
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions 
 * are met:
 * 
 * o Redistributions of source code must retain the above copyright notice, 
 *   this list of conditions and the following disclaimer.
 * o Redistributions in binary form must reproduce the above copyright notice,
 *   this list of conditions and the following disclaimer in the documentation
 *   and/or other materials provided with the distribution.
 * o Neither the name of the Softisland nor the names of its contributors
 *   may be used to endorse or promote products derived from this software
 *   without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS 
 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT 
 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR 
 * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT 
 * HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, 
 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT 
 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, 
 * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY 
 * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT 
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE 
 * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 */
/**
 * Enter description here...
 *
 * @package     File
 * @copyright   Slawomir Mazur (c) 2010 (http://www.zend.com/zce.php?c=ZEND011643)
 * @license     http://www.opensource.org/licenses/bsd-license.php
 * @author      Slawomir Mazur <mazursllawek@gmail.com>
 */

class Gp_File_Excel extends Gp_File_Abstract
{	
	public function __construct($id)
	{
		parent::__construct($id);
	}
	
	public function parse()
	{
		$this->_initFileSpecs();
		
		/* @var $res PHPExcel */
		$result = $this->_reader->load($this->_fileName);
		/* @var $objWorksheet PHPExcel_Worksheet */
		$objWorksheet = $result->getActiveSheet();
		
		$_result = array();
		
		$startRow = (((int) $this->_startRow) >= 2)? $this->_startRow : 2;		
		$endRow = (($this->_endRow) && (((int) $this->_endRow) <= $objWorksheet->getHighestRow()))? $this->_endRow : $objWorksheet->getHighestRow();
		
		$highestRow = $objWorksheet->getHighestRow(); // e.g. 10
		$highestColumn = $objWorksheet->getHighestColumn(); // e.g 'F'
		
		$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); // e.g. 5
		
		
		// column filtering
		$columns = range(0, $highestColumnIndex);
		
		if (false !== $this->_columns) {
			$columns = $this->_columns;
		}
		
		$rows = array();
		/* @var $historyModel System_Model_History */
		$historyModel = Gp_App::getModel('system/history');
		
		$queryStart = 'INSERT INTO '.$historyModel->getName().' (
                library_id, gene_id, attribute_id, value ) values ';
        $queryEnd = '';
        
        $write = $historyModel->getAdapter();        
        $write->beginTransaction();
        
        try {        
			// default or rows boundary filtering
			if (($this->_loadWhole) || (false !== $this->_startRow) || (false !== $this->_endRow)) {
				for ($row = $startRow; $row <= $endRow; ++$row) {
					foreach ($columns as $colIndex => $col) {
						
						$geneId = trim($objWorksheet->getCellByColumnAndRow($this->_geneIdColumnIndex, $row)->getValue());
						
						if (empty($geneId)) continue 2;
						
						$rows[] = "('".implode("', '", array(
							$this->_fileInstance->library_id,
							$geneId,
							$this->_columnsMap['db'][$colIndex],
							$objWorksheet->getCellByColumnAndRow($col, $row)->getValue()
						))."')";
						
						if (sizeof($rows)==1000) {
							$sql = $queryStart.join(',', $rows).$queryEnd;
							$write->query($sql);
							$rows = array();
						}
				  	}
				}
				if (!empty($rows)) {
	                $sql = $queryStart.join(',', $rows).$queryEnd;
	                $write->query($sql);
	            }
	
	            $write->commit();
	            
				return;
			}
			
			// read only rows
			if (false !== $this->_readRows) {
				foreach ($this->_readRows as $row) {
					foreach ($columns as $col) {
						$geneId = trim($objWorksheet->getCellByColumnAndRow($this->_geneIdColumnIndex, $row)->getValue());
						
						if (empty($geneId)) continue 2;
						
						$rows[] = "('".implode("', '", array(
							$this->_fileInstance->library_id,
							$geneId,
							$this->_columnsMap['db'][$colIndex],
							$objWorksheet->getCellByColumnAndRow($col, $row)->getValue()
						))."')";
						
						if (sizeof($rows)==1000) {
							$sql = $queryStart.join(',', $rows).$queryEnd;
							$write->query($sql);
							$rows = array();
						}
				  	}
				}
				if (!empty($rows)) {
	                $sql = $queryStart.join(',', $rows).$queryEnd;
	                $write->query($sql);
	            }
	
	            $write->commit();
	            
				return;
			}
			
			if (false !== $this->_omitRows) {
				for ($row = 2; $row <= $highestRow; ++$row) {
					// skip row
					if (isset($this->_omitRows[$row])) continue;
	
					foreach ($columns as $col) {
						$geneId = trim($objWorksheet->getCellByColumnAndRow($this->_geneIdColumnIndex, $row)->getValue());
						
						if (empty($geneId)) continue 2;
						
						$rows[] = "('".implode("', '", array(
							$this->_fileInstance->library_id,
							$geneId,
							$this->_columnsMap['db'][$colIndex],
							$objWorksheet->getCellByColumnAndRow($col, $row)->getValue()
						))."')";
						
						if (sizeof($rows)==1000) {
							$sql = $queryStart.join(',', $rows).$queryEnd;
							$write->query($sql);
							$rows = array();
						}
				  	}
				}
				if (!empty($rows)) {
	                $sql = $queryStart.join(',', $rows).$queryEnd;
	                $write->query($sql);
	            }
	
	            $write->commit();
	            
				return;
			}		
        } catch (Exception $e) {
        	$write->rollback();
            throw $e;
        }
        
        return $this;
	}
	
	/**
	 * 
	 * Load first row from document so called headers
	 * 
	 * @return array
	 */
	public function getDocumentColumnNames()
	{
		$this->_initFileSpecs();
		
		$headerFilter = new Gp_File_Excel_Filter_Header();
		
		$this->_reader->setReadFilter($headerFilter);
		
		/* @var $res PHPExcel */
		$result = $this->_reader->load($this->_fileName);
		$objWorksheet = $result->getActiveSheet();
		
		$highestRow = $objWorksheet->getHighestRow(); // e.g. 10
		$highestColumn = $objWorksheet->getHighestColumn(); // e.g 'F'
		
		$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); // e.g. 5
		
		$columns = array();
		for ($row = 1; $row <= 1; ++$row) {
			for ($col = 0; $col < $highestColumnIndex; ++$col) {
		    	$columns[$col] = $objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
		  	}
		}
		
		return $columns;
	}
	
	/**
	 * 
	 * Get file name and type
	 */
	protected function _initFileSpecs()
	{
		if (empty($this->_fileInstance)) {
			throw new Exception('No file selected');
		}
		
		$this->_fileName = self::BASIC_EXCEL_UPLOAD_PATH . DS . $this->_fileInstance->name;
		
		// check whether file exists
		if (!file_exists($this->_fileName)) {
			throw new Exception('File "' . $this->_fileName . '" do not exists');
		}
		
		$fileType = PHPExcel_IOFactory::identify($this->_fileName);
		
		$this->_reader = PHPExcel_IOFactory::createReader($fileType);
		// suppress error if method not exists ex.: CSV		
		if (method_exists($this->_reader, 'setReadDataOnly')) {
			$this->_reader->setReadDataOnly(true);
		}
		
		return $this;
	}
}